import logging
import traceback
import pymysql
from sqlalchemy import create_engine, MetaData, Table, Column, distinct
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.pool import NullPool
from sqlalchemy.dialects.mysql import INTEGER, VARCHAR, TEXT
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
logging.basicConfig(format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s',
                    level=logging.INFO,
                    filename='log/AmazonCrawler.log',
                    filemode='a')


class Amazon(Base):  # 继承生成的orm基类
    __tablename__ = "amazon_reviews"  # 表名
    review_id = Column(VARCHAR(255), primary_key=True, nullable=True)  # 设置主键
    profile_name = Column(VARCHAR(255))
    review_date = Column(VARCHAR(255))
    review_body = Column(TEXT)
    page = Column(INTEGER)
    asin = Column(VARCHAR(20))

    def __init__(self, review_id, profile_name, review_date, review_body, page, asin):
        self.review_id = review_id
        self.profile_name = profile_name
        self.review_date = review_date
        self.review_body = review_body
        self.page = page
        self.asin = asin


class db_uitl():
    def __init__(self):
        # 初始化数据库连接，使用pymysql模块
        self.engine = create_engine("mysql+pymysql://{}:{}@{}/{}?charset={}?connect_timeout=10"
                                    .format('root',
                                            '',
                                            '127.0.0.1',
                                            # 'amazonreviewstest',
                                            # '172.17.0.2:3306',
                                            'lj',
                                            'utf8mb4')
                                    , poolclass=NullPool)
        # 创建DBSession类型
        self.DBSession = sessionmaker(bind=self.engine)
        # sqlalchemy连接数据库的session
        self.db_session = self.DBSession

    """ 插入数据库 """

    def into_db(self, data):
        try:
            session = self.db_session()
            session.execute(Amazon.__table__.insert(), data)
            session.commit()
            session.close()
            return True
        except:
            err = str(traceback.format_exc()) + ' | ' + str(data)
            logging.info(err)
            return False

    """ 查询该asin爬取的评论数 """

    def get_db_asin_count(self, asin):
        session = self.db_session()
        content_list = session.query(Amazon).filter(Amazon.asin == asin).all()
        session.close()
        return len(content_list)
